﻿using System;
using System.Data;
using System.Data.SQLite;
using System.Collections.Generic;

class Program
{
    static string connectionString = "Data Source=school.db;Version=3;";

    static void Main(string[] args)
    {
        CreateTables();
        while (true)
        {
            Console.WriteLine("请选择操作:");
            Console.WriteLine("1. 学校管理");
            Console.WriteLine("2. 班级管理");
            Console.WriteLine("3. 学生管理");
            Console.WriteLine("4. 查看操作日志");
            Console.WriteLine("5. 退出");
            string choice = Console.ReadLine();

            switch (choice)
            {
                case "1":
                    SchoolManagement();
                    break;
                case "2":
                    ClassManagement();
                    break;
                case "3":
                    StudentManagement();
                    break;
                case "4":
                    ViewLogs();
                    break;
                case "5":
                    return;
                default:
                    Console.WriteLine("无效的选择，请重新输入!");
                    break;
            }
        }
    }

    // 创建数据库表
    static void CreateTables()
    {
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            connection.Open();
            string createSchoolTable = @"
                CREATE TABLE IF NOT EXISTS School (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL,
                    Address TEXT
                );";
            string createClassTable = @"
                CREATE TABLE IF NOT EXISTS Class (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL,
                    SchoolId INTEGER,
                    FOREIGN KEY(SchoolId) REFERENCES School(Id)
                );";
            string createStudentTable = @"
                CREATE TABLE IF NOT EXISTS Student (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL,
                    Age INTEGER,
                    ClassId INTEGER,
                    FOREIGN KEY(ClassId) REFERENCES Class(Id)
                );";
            string createLogTable = @"
                CREATE TABLE IF NOT EXISTS Log (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Operation TEXT NOT NULL,
                    Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
                );";

            using (SQLiteCommand command = new SQLiteCommand(createSchoolTable, connection))
            {
                command.ExecuteNonQuery();
            }
            using (SQLiteCommand command = new SQLiteCommand(createClassTable, connection))
            {
                command.ExecuteNonQuery();
            }
            using (SQLiteCommand command = new SQLiteCommand(createStudentTable, connection))
            {
                command.ExecuteNonQuery();
            }
            using (SQLiteCommand command = new SQLiteCommand(createLogTable, connection))
            {
                command.ExecuteNonQuery();
            }
        }
    }

    // 记录日志
    static void LogOperation(string operation)
    {
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            connection.Open();
            string query = "INSERT INTO Log (Operation) VALUES (@operation)";
            using (SQLiteCommand command = new SQLiteCommand(query, connection))
            {
                command.Parameters.AddWithValue("@operation", operation);
                command.ExecuteNonQuery();
            }
        }
    }

    // 查看操作日志
    static void ViewLogs()
    {
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            connection.Open();
            string query = "SELECT * FROM Log";
            using (SQLiteCommand command = new SQLiteCommand(query, connection))
            {
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"{reader["Id"]}: {reader["Operation"]} - {reader["Timestamp"]}");
                    }
                }
            }
        }
    }

    // 学校管理
    static void SchoolManagement()
    {
        Console.WriteLine("1. 添加学校");
        Console.WriteLine("2. 查看学校");
        Console.WriteLine("3. 返回");

        string choice = Console.ReadLine();
        if (choice == "1")
        {
            Console.Write("请输入学校名称: ");
            string name = Console.ReadLine();
            Console.Write("请输入学校地址: ");
            string address = Console.ReadLine();

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string query = "INSERT INTO School (Name, Address) VALUES (@name, @address)";
                using (SQLiteCommand command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@name", name);
                    command.Parameters.AddWithValue("@address", address);
                    command.ExecuteNonQuery();
                }
            }
            LogOperation($"添加学校: {name}");
            Console.WriteLine("学校添加成功!");
        }
        else if (choice == "2")
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string query = "SELECT * FROM School";
                using (SQLiteCommand command = new SQLiteCommand(query, connection))
                {
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine($"学校ID: {reader["Id"]}, 名称: {reader["Name"]}, 地址: {reader["Address"]}");
                        }
                    }
                }
            }
        }
        else if (choice == "3")
        {
            return;
        }
    }

    // 班级管理
    static void ClassManagement()
    {
        Console.WriteLine("1. 添加班级");
        Console.WriteLine("2. 查看班级");
        Console.WriteLine("3. 返回");

        string choice = Console.ReadLine();
        if (choice == "1")
        {
            Console.Write("请输入班级名称: ");
            string name = Console.ReadLine();
            Console.Write("请输入所属学校ID: ");
            int schoolId = int.Parse(Console.ReadLine());

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string query = "INSERT INTO Class (Name, SchoolId) VALUES (@name, @schoolId)";
                using (SQLiteCommand command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@name", name);
                    command.Parameters.AddWithValue("@schoolId", schoolId);
                    command.ExecuteNonQuery();
                }
            }
            LogOperation($"添加班级: {name}");
            Console.WriteLine("班级添加成功!");
        }
        else if (choice == "2")
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string query = "SELECT * FROM Class";
                using (SQLiteCommand command = new SQLiteCommand(query, connection))
                {
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine($"班级ID: {reader["Id"]}, 名称: {reader["Name"]}, 所属学校ID: {reader["SchoolId"]}");
                        }
                    }
                }
            }
        }
        else if (choice == "3")
        {
            return;
        }
    }

    // 学生管理
    static void StudentManagement()
    {
        Console.WriteLine("1. 添加学生");
        Console.WriteLine("2. 查看学生");
        Console.WriteLine("3. 返回");

        string choice = Console.ReadLine();
        if (choice == "1")
        {
            Console.Write("请输入学生名称: ");
            string name = Console.ReadLine();
            Console.Write("请输入学生年龄: ");
            int age = int.Parse(Console.ReadLine());
            Console.Write("请输入所属班级ID: ");
            int classId = int.Parse(Console.ReadLine());

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string query = "INSERT INTO Student (Name, Age, ClassId) VALUES (@name, @age, @classId)";
                using (SQLiteCommand command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@name", name);
                    command.Parameters.AddWithValue("@age", age);
                    command.Parameters.AddWithValue("@classId", classId);
                    command.ExecuteNonQuery();
                }
            }
            LogOperation($"添加学生: {name}");
            Console.WriteLine("学生添加成功!");
        }
        else if (choice == "2")
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string query = "SELECT * FROM Student";
                using (SQLiteCommand command = new SQLiteCommand(query, connection))
                {
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine($"学生ID: {reader["Id"]}, 名称: {reader["Name"]}, 年龄: {reader["Age"]}, 所属班级ID: {reader["ClassId"]}");
                        }
                    }
                }
            }
        }
        else if (choice == "3")
        {
            return;
        }
    }
}